#include "CDataSourceSQLite.h"

#include <QDebug>
#include <QSqlQuery>
#include <QSqlError>

CDataSourceSQLite::CDataSourceSQLite()
{
    m_db = QSqlDatabase::addDatabase("QSQLITE"); //连接数据库
    m_db.setDatabaseName("./stuInfoDB.db"); //设置数据库名称

    //打开数据库
    if(!m_db.open())
    {
        qDebug() << "Failed To Open DataBase!";
        return;
    }
    qDebug() << "Success To Open DataBase!";

    //创建数据表
    QSqlQuery query;
    QString sql = QString("CREATE TABLE IF NOT EXISTS tb_stuInfo"
                          "(id int primary key not null,"
                          "name varchar(50),"
                          "sex varchar(5),"
                          "phone varchar(15),"
                          "cet4 int,"
                          "gpa real,"
                          "overallScore real);");
    if(!query.exec(sql))
    {
        qDebug() << "Failed To Creat Table!";
        qDebug() << query.lastError();;
        return;
    }
    qDebug() << "Success To Create Table!";

    //关闭数据库
    m_db.close();
}

CDataSourceSQLite::~CDataSourceSQLite()
{

}

bool CDataSourceSQLite::selectStuinfos(QList<CStuInfo> &stuInfoList)
{
    if(!m_db.open())
    {
        qDebug() << "Failed To Open DataBase: selectStuinfos!";
        return false;
    }

    //查询数据
    QSqlQuery query;
    QString sql = "SELECT * FROM tb_stuInfo";
    if(!query.exec(sql))
    {
        qDebug() << "Failed To Select From tb_stuInfo";
        return  false;
    }

    //取出数据
    while(query.next())
    {
        CStuInfo stuInfo;
        int id = query.value("id").toInt();
        QString name = query.value("name").toString();
        QString sex = query.value("sex").toString();
        QString phone = query.value("phone").toString();
        int cet4 = query.value("cet4").toInt();
        double gpa = query.value("gpa").toDouble();
        stuInfo.setdata(id, name, sex, phone, cet4, gpa);
        stuInfoList.append(stuInfo);
    }

    m_db.close();
    return true;
}

bool CDataSourceSQLite::addStuInfo(CStuInfo &stuInfo)
{
    if(!m_db.open())
    {
        qDebug() << "Failed To Open DataBase: addStuinfos!";
        return false;
    }

    //把数据存入到数据库的数据表中
    QSqlQuery query;
    query.prepare("INSERT INTO tb_stuInfo (id, name, sex, phone, cet4, gpa, overallScore) "
                  "values(:id, :name, :sex, :phone, :cet4, :gpa, :overallScore)");
    query.bindValue(":id", stuInfo.id());
    query.bindValue(":name", stuInfo.name());
    query.bindValue(":sex", stuInfo.sex());
    query.bindValue(":phone", stuInfo.phone());
    query.bindValue(":cet4", stuInfo.cet4());
    query.bindValue(":gpa", stuInfo.gpa());
    query.bindValue(":overallScore", stuInfo.overallScore());
    if(!query.exec())
    {
        qDebug() << query.lastError();
        m_db.close();
        return false;
    }

    qDebug() << "数据库添加数据成功!";
    m_db.close();
    return true;
}

bool CDataSourceSQLite::updateStuInfo(CStuInfo &stuInfo)
{
    if(!m_db.open())
    {
        qDebug() << "Failed To Open DataBase: addStuinfos!";
        return false;
    }

    //把数据修改后存入到数据库的数据表中
    QSqlQuery query;
    query.prepare("UPDATE tb_stuInfo SET name = :name, sex = :sex, phone = :phone, cet4 = :cet4,"
                  " gpa = :gpa, overallScore = :overallScore WHERE id = :id");
    query.bindValue(":id", stuInfo.id());
    query.bindValue(":name", stuInfo.name());
    query.bindValue(":sex", stuInfo.sex());
    query.bindValue(":phone", stuInfo.phone());
    query.bindValue(":cet4", stuInfo.cet4());
    query.bindValue(":gpa", stuInfo.gpa());
    query.bindValue(":overallScore", stuInfo.overallScore());
    if(!query.exec())
    {
        qDebug() << query.lastError();
        m_db.close();
        return false;
    }

    qDebug() << "数据库修改数据成功!";
    m_db.close();
    return true;
}

bool CDataSourceSQLite::deleteStuInfo(int id)
{
    if(!m_db.open())
    {
        qDebug() << "Failed To Open DataBase: deleteStuinfos!";
        return false;
    }

    QSqlQuery query;
    QString sql = QString("DELETE FROM tb_stuInfo where id = %1").arg(id);
    if(!query.exec(sql))
    {
        qDebug() << "Failed To Delete StuInfo!";
        qDebug() << query.lastError();
        m_db.close();
        return false;
    }

    qDebug() << "数据库删除数据成功!";
    m_db.close();
    return true;
}
